#!/usr/bin/env python3

"""
将数据库的内容导出到excel表格
1. 指定数据库,指定数据表,指定表字段
2. 整合信息导出的excel表
"""

from db import Db
import xlwt

T2 = {
    "收款人账号":"recv_account",
    "收款人户名":"recv_name",
    "收款人开户行":"recv_bank",
    "银联号":"unionpay_num",
    "币种(CNY: 人民币)":"currency",
    "金额":"amount",
    "交易类型(0:对公;1:对私)":"trade_type",
    "收款人类型(0:行内;1:跨行)":"recv_type",
    "转账用途":"transfer_purpose"
}
TITLES = [
    "收款人账号",
    "收款人户名",
    "收款人开户行",
    "银联号",
    "币种(CNY: 人民币)",
    "金额",
    "交易类型(0:对公;1:对私)",
    "收款人类型(0:行内;1:跨行)",
    "转账用途"
]

class OutExcel():

    def __init__(self, db="mysql" ,tb="demo", xls_file=None):
        self.db = db
        self.tb = tb
        self.xls_file = "%s.xls" % tb if xls_file is None else xls_file
        self.conn = Db(db,port=3306)

    def getTitle(self):
        """
        获取表字段
        """
        sql = "select column_name from information_schema.columns where table_schema='%s' and table_name='%s';"
        titles = self.conn.query(sql % (self.db, self.tb))
        return [obj['column_name'] for obj in titles]

    def getContent(self, columns):
        """
        获取表格的数据
        """
        contents = self.conn.query("select %s from %s.%s;" % \
                (','.join(columns), self.db, self.tb))
        return contents

    def set_style(self, font_name, height, bold=False):
        """
        设置excel的样式
        """
        # 初始化样式
        style = xlwt.XFStyle()

        # 为样式创建字体
        font = xlwt.Font()
        # 字体'Times New Roman'
        font.name = font_name
        font.bold = bold
        font.color_index = 4
        font.height = height

        style.font = font

        return style

    def outToXls(self, contents):
        """
        将字段名和对应的数据写入表格中
        """
        #创建工作簿
        f = xlwt.Workbook(encoding='utf-8')

        # 创建第一个sheet: sheet1
        sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True)

        #  设置第1,3列宽度
        col_1 = sheet1.col(0)
        col_3 = sheet1.col(2)
        col_1.width = 256 * 20
        col_3.width = 256 * 20
        #  设置第一行的行高
        row_1 = sheet1.row(0)
        row_1.height = 400

        #生成第一行: 表的字段
        # 按列插入数据
        #  sheet1.write(0, i, v, self.set_style('Times New Roman',120,True))
        for i in range(len(TITLES)):
            sheet1.write(0, i, TITLES[i], self.set_style('宋体', 220, True))
            for j in range(len(contents)):
                k = T2[TITLES[i]]
                sheet1.write(j+1, i, contents[j][k])

        #保存文件
        f.save(self.xls_file)


if __name__ == "__main__":
    oe = OutExcel("barda", "bd_withdraw", "/home/molio/Downloads/demo.xls")
    columns = [
        'recv_account',
        'recv_name',
        'recv_bank',
        'unionpay_num',
        'currency',
        'amount',
        'trade_type',
        'recv_type',
        'transfer_purpose'
    ]
    contents = oe.getContent(columns)
    oe.outToXls(contents)
